create table TravelGuidePrice
(
	 [TravelGuidePriceId] int identity(1, 1) primary key
	,[TravelGuidePriceName] nvarchar(256)
	,[Level] int 
	,[LanguageId] int
	,[ScheduleId] int
	,[Price] decimal
	,[CreatedBy] int
	,[CreatedDate] datetime
	,[UpdatedBy] int
	,[UpdatedDate] datetime
	,[Status] int
)
go

CREATE PROCEDURE TravelGuidePriceGet
	@TravelGuidePriceId int	
AS
SELECT *
FROM [TravelGuidePrice]
WHERE [TravelGuidePrice].[TravelGuidePriceId] = @TravelGuidePriceId
GO

CREATE PROCEDURE TravelGuidePriceList
AS
SELECT *
FROM [TravelGuidePrice]
ORDER BY TravelGuidePriceId DESC
GO

ALTER PROCEDURE TravelGuidePriceInsert
	 @TravelGuidePriceName nvarchar(256)
	,@Level int
	,@LanguageId int
	,@ScheduleId int
	,@Price decimal(18, 0)
	,@CreatedBy int
	,@CreatedDate datetime
	,@UpdatedBy int
	,@UpdatedDate datetime
	,@Status int
AS

INSERT INTO TravelGuidePrice (
	 [TravelGuidePriceName]
	,[Level]
	,[LanguageId]
	,[ScheduleId]
	,[Price]
	,[CreatedBy]
	,[CreatedDate]
	,[UpdatedBy]
	,[UpdatedDate]
	,[Status]
	
) VALUES (
	 @TravelGuidePriceName
	,@Level
	,@LanguageId
	,@ScheduleId
	,@Price
	,@CreatedBy
	,@CreatedDate
	,@UpdatedBy
	,@UpdatedDate
	,@Status
	
)

select SCOPE_IDENTITY()
GO

ALTER PROCEDURE TravelGuidePriceUpdate
	@TravelGuidePriceId int, 
	@TravelGuidePriceName nvarchar(256),
	@Level int, 
	@LanguageId int, 
	@ScheduleId int, 
	@Price decimal(18, 0), 
	@CreatedBy int, 
	@CreatedDate datetime, 
	@UpdatedBy int, 
	@UpdatedDate datetime, 
	@Status int 
AS
UPDATE TravelGuidePrice SET
	    [TravelGuidePriceName] = @TravelGuidePriceName
	   ,[Level] = @Level
	   ,[LanguageId] = @LanguageId
	   ,[ScheduleId] = @ScheduleId
	   ,[Price] = @Price
	   ,[CreatedBy] = @CreatedBy
	   ,[CreatedDate] = @CreatedDate
	   ,[UpdatedBy] = @UpdatedBy
	   ,[UpdatedDate] = @UpdatedDate
	   ,[Status] = @Status
WHERE [TravelGuidePrice].[TravelGuidePriceId] = @TravelGuidePriceId
GO

CREATE PROCEDURE TravelGuidePriceDelete
	@TravelGuidePriceId int    
AS
DELETE FROM TravelGuidePrice
WHERE [TravelGuidePrice].[TravelGuidePriceId] = @TravelGuidePriceId
GO

create proc TravelGuidePriceListByTravelGuide
	 @TravelGuideId int
as
declare @Level int
declare @Languages varchar(64)
select @Level = [Level], @Languages = Languages from TravelGuide where TravelGuideId = @TravelGuideId

declare @SqlCommand nvarchar(max)
set @SqlCommand = 'select * from TravelGuidePrice where Level = ' + CONVERT(varchar(8), @Level) + ' and LanguageId in (' + @Languages + ')'
exec sp_executesql @SqlCommand
go